package views.fee.employer;

import org.intellij.lang.annotations.Language;
import tools.DateChooser;
import tools.Icons;
import utils.DBUtil;

import javax.swing.*;
import javax.swing.table.AbstractTableModel;
import javax.swing.table.DefaultTableCellRenderer;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

public class FeeEm_Update_Delete extends JFrame {
    int employer_id, fee_remark;
    ImageIcon imageIcon = new ImageIcon("src/images/fee.png");
    private String[] table_title = {"编号", "用人单位名称", "交费金额", "交费日期", "备注"};
    private JScrollPane jsp = new JScrollPane();
    private JTable table = new JTable();
    private JButton btn_change = new JButton("修改");
    private JButton btn_delete = new JButton("删除");
    private MyModel model;
    private JLabel jL_id = new JLabel("编号:");
    private JLabel jl_employername = new JLabel("用人单位名称：");
    private JLabel jl_money = new JLabel("交费金额：");
    private JLabel jl_date = new JLabel("交费日期：");
    private JLabel jl_remark = new JLabel("备注：");
    private JTextField jT_id = new JTextField();
    private JComboBox<String> jC_employername = new JComboBox<String>();
    private JTextField jT_money = new JTextField();
    private JTextField jT_date = new JTextField();
    private JComboBox<String> jC_remark = new JComboBox<String>();
    private JButton btn_find = new JButton("查找");
    private JTextField jT_find = new JTextField();
    private Panel panBtn = new Panel();
    private Panel panLab = new Panel();
    DateChooser dateChooser = DateChooser.getInstance("yyyy-MM-dd");
    Icons icons = new Icons();

    public FeeEm_Update_Delete() {
        this.setSize(600, 550);
        setTitle("用人单位交费信息修改与删除");
        setIconImage(imageIcon.getImage().getScaledInstance(80, 80, Image.SCALE_DEFAULT));
        setLocationRelativeTo(null);
        setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);

        panBtn.add(btn_change);
        panBtn.add(btn_delete);
        // 设置中间的panel布局为空
        panLab.setLayout(null);

        JRadioButton JR_findByEmployername = new JRadioButton("按用人单位名称查找");
        JRadioButton JR_findByfee = new JRadioButton("按交费情况查找");
        ButtonGroup bg = new ButtonGroup();
        JR_findByEmployername.setSelected(true);
        bg.add(JR_findByEmployername);
        bg.add(JR_findByfee);
        JR_findByEmployername.setSize(160, 30);
        JR_findByEmployername.setLocation(40, 20);

        JR_findByfee.setSize(120, 30);
        JR_findByfee.setLocation(200, 20);

        // 给组件设置位置
        jT_find.setSize(140, 30);
        jT_find.setLocation(350, 20);

        btn_find.setSize(80, 30);
        btn_find.setLocation(500, 20);

        //TODO：位置
        jL_id.setSize(100, 30);
        jL_id.setLocation(20, 60);
        jL_id.setHorizontalAlignment(JLabel.RIGHT);
        jT_id.setSize(150, 30);
        jT_id.setLocation(120, 60);

        jl_employername.setSize(100, 30);
        jl_employername.setLocation(280, 60);
        jl_employername.setHorizontalAlignment(JLabel.RIGHT);

        try {
            for (int k = 0; k < getEmployer_name().size(); k++) {
                jC_employername.addItem(getEmployer_name().get(k));
            }
            jC_employername.setSize(150, 30);
            jC_employername.setLocation(380, 60);
        } catch (SQLException e) {
            e.printStackTrace();
        }


        jl_money.setSize(100, 30);
        jl_money.setLocation(20, 90);
        jl_money.setHorizontalAlignment(JLabel.RIGHT);
        jT_money.setSize(150, 30);
        jT_money.setLocation(120, 90);

        jl_date.setSize(100, 30);
        jl_date.setLocation(280, 90);
        jl_date.setHorizontalAlignment(JLabel.RIGHT);
        jT_date.setSize(150, 30);
        jT_date.setLocation(380, 90);
        dateChooser.register(jT_date);

        jl_remark.setSize(100, 30);
        jl_remark.setLocation(140, 120);
        jl_remark.setHorizontalAlignment(JLabel.RIGHT);
        try {
            for (int k = 0; k < getfeerepresent_name().size(); k++) {
                jC_remark.addItem(getfeerepresent_name().get(k));
            }
            jC_remark.setSize(150, 30);
            jC_remark.setLocation(240, 120);
        } catch (SQLException e) {
            e.printStackTrace();
        }


        model = new MyModel("select fee_employer.id, employer_name, pay_amount, paid_date, express from fee_employer" +
                " LEFT JOIN fee_representation fr on fr.id = fee_employer.remark " +
                "LEFT JOIN employer e on e.employer_id = fee_employer.employer_id");
        table.setModel(model);
        // 修改列名称
        for (int i = 0; i < table_title.length; i++) {
            table.getColumnModel().getColumn(i).setHeaderValue(table_title[i]);
        }
        DefaultTableCellRenderer r = new DefaultTableCellRenderer();
        r.setHorizontalAlignment(JLabel.CENTER);
        table.setDefaultRenderer(Object.class, r);
        // 设置可见视图的接口
        jsp.setViewportView(table);
        // 定义表格 宽600，高度200
        jsp.setPreferredSize(new Dimension(600, 300));
        // 设置横向和垂直滚动条可见
        jsp.setVerticalScrollBarPolicy(JScrollPane.VERTICAL_SCROLLBAR_ALWAYS);
        jsp.setHorizontalScrollBarPolicy(JScrollPane.HORIZONTAL_SCROLLBAR_ALWAYS);


        // 把标签和文本框加到panLab面板中
        panLab.add(jL_id);
        panLab.add(jT_id);

        panLab.add(jl_employername);
        panLab.add(jC_employername);

        panLab.add(jl_money);
        panLab.add(jT_money);

        panLab.add(jl_date);
        panLab.add(jT_date);

        panLab.add(jl_remark);
        panLab.add(jC_remark);
        panLab.add(JR_findByEmployername);
        panLab.add(JR_findByfee);
        panLab.add(jT_find);
        panLab.add(btn_find);

        this.add(jsp, BorderLayout.NORTH);
        this.add(panLab, BorderLayout.CENTER);
        this.add(panBtn, BorderLayout.SOUTH);

        jT_id.setEditable(false);
        // 获取表里的值
        table.addMouseListener(new MouseAdapter() {
            @Override
            public void mouseClicked(MouseEvent e) {
                int selRow = table.getSelectedRow();
                String Id = table.getValueAt(selRow, 0).toString().trim();
                String employer_name = table.getValueAt(selRow, 1).toString().trim();
                String money = table.getValueAt(selRow, 2).toString().trim();
                String date = table.getValueAt(selRow, 3).toString().trim();
                String remark = table.getValueAt(selRow, 4).toString().trim();

                jT_id.setText(Id);
                jC_employername.setSelectedItem(employer_name);
                jT_money.setText(money);
                jT_date.setText(date);
                jC_remark.setSelectedItem(remark);
            }
        });

        btn_find.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent arg0) {
                String s = jT_find.getText().trim();
                if (JR_findByEmployername.isSelected()) {
                    @Language("MySQL")
                    String sql = "select fee_employer.id, employer_name, pay_amount, paid_date, express from fee_employer " +
                            "LEFT JOIN employer e on e.employer_id = fee_employer.employer_id " +
                            "LEFT JOIN fee_representation fr on fr.id = fee_employer.remark" +
                            " where employer_name like " +
                            "'%" + s + "%'";
                    model = new MyModel(sql);
                    table.setModel(model);
                    for (int i = 0; i < table_title.length; i++) {
                        table.getColumnModel().getColumn(i).setHeaderValue(table_title[i]);
                    }
                    DefaultTableCellRenderer r = new DefaultTableCellRenderer();
                    r.setHorizontalAlignment(JLabel.CENTER);
                    table.setDefaultRenderer(Object.class, r);
                } else if (JR_findByfee.isSelected()) {
                    @Language("MySQL")
                    String sql = "select fee_employer.id, employer_name, pay_amount, paid_date, express from fee_employer " +
                            "LEFT JOIN employer e on e.employer_id = fee_employer.employer_id " +
                            "LEFT JOIN fee_representation fr on fr.id = fee_employer.remark" +
                            " where express like " +
                            "'%" + s + "%'";
                    model = new MyModel(sql);
                    table.setModel(model);
                    for (int i = 0; i < table_title.length; i++) {
                        table.getColumnModel().getColumn(i).setHeaderValue(table_title[i]);
                    }
                    DefaultTableCellRenderer r = new DefaultTableCellRenderer();
                    r.setHorizontalAlignment(JLabel.CENTER);
                    table.setDefaultRenderer(Object.class, r);
                } else {
                    @Language("MySQL")
                    String sql = "select fee_employer.id, employer_name, pay_amount, paid_date, express from fee_employer " +
                            "LEFT JOIN employer e on e.employer_id = fee_employer.employer_id " +
                            "LEFT JOIN fee_representation fr on fr.id = fee_employer.remark";
                    model = new MyModel(sql);
                    table.setModel(model);
                    for (int i = 0; i < table_title.length; i++) {
                        table.getColumnModel().getColumn(i).setHeaderValue(table_title[i]);
                    }
                    DefaultTableCellRenderer r = new DefaultTableCellRenderer();
                    r.setHorizontalAlignment(JLabel.CENTER);
                    table.setDefaultRenderer(Object.class, r);
                }
            }
        });

        btn_change.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent arg0) {
                int id = Integer.parseInt(jT_id.getText());
                String employername = jC_employername.getSelectedItem().toString();
                try {
                    employer_id = SelectEmlployerId(employername);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                String money = jT_money.getText().trim();
                String date = jT_date.getText().trim();
                String remark = jC_remark.getSelectedItem().toString();
                try {
                    fee_remark = selefeerepresent_id(remark);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                Statement stmt = DBUtil.getStatement();
                String sql = "update fee_employer set employer_id = "
                        + employer_id
                        + ",pay_amount='"
                        + money
                        + "',paid_date='"
                        + date
                        + "',remark="
                        + fee_remark
                        + " where id="
                        + id
                        + ";";
                try {
                    stmt.executeUpdate(sql);
                    JOptionPane.showMessageDialog(null, "修改成功。", null, JOptionPane.INFORMATION_MESSAGE, icons.getOkImage());
                    jT_id.setText("");
                    jC_employername.setSelectedIndex(-1);
                    jT_money.setText("");
                    jT_date.setText("");
                    jC_remark.setSelectedIndex(-1);
                    // 实时更新表
                    @Language("MySQL")
                    String updateSQL = "SELECT fee_employer.id, employer_name, pay_amount, paid_date, express from fee_employer " +
                            "LEFT JOIN fee_representation fr on fr.id = fee_employer.remark " +
                            "LEFT JOIN employer e on e.employer_id = fee_employer.employer_id";
                    model = new MyModel(updateSQL);
                    table.setModel(model);
                    for (int i = 0; i < table_title.length; i++) {
                        table.getColumnModel().getColumn(i).setHeaderValue(table_title[i]);
                    }
                    DefaultTableCellRenderer r = new DefaultTableCellRenderer();
                    r.setHorizontalAlignment(JLabel.CENTER);
                    table.setDefaultRenderer(Object.class, r);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        });
        btn_delete.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent arg0) {
                int i = Integer.parseInt(jT_id.getText().trim());
                int result = JOptionPane.showConfirmDialog(null, "确定删除吗?", "提示", JOptionPane.DEFAULT_OPTION, JOptionPane.QUESTION_MESSAGE, icons.getQuestionImage());
                if (result == JOptionPane.YES_OPTION) {
                    Statement stmt = DBUtil.getStatement();
                    @Language("MySQL")
                    String sql = "delete from fee_employer where fee_employer.id = " + i + ";";

                    try {
                        stmt.executeUpdate(sql);
                        JOptionPane.showConfirmDialog(null, "已删除！", null, JOptionPane.OK_OPTION, JOptionPane.INFORMATION_MESSAGE, icons.getOkImage());
                        // 实时更新表格数据
                        @Language("MySQL")
                        String sql2 = "SELECT fee_employer.id, employer_name, pay_amount, paid_date, express from fee_employer " +
                                "LEFT JOIN fee_representation fr on fr.id = fee_employer.remark " +
                                "LEFT JOIN employer e on e.employer_id = fee_employer.employer_id";
                        model = new MyModel(sql2);
                        table.setModel(model);
                        for (int k = 0; k < table_title.length; k++) {
                            table.getColumnModel().getColumn(k).setHeaderValue(table_title[k]);
                        }
                        DefaultTableCellRenderer r = new DefaultTableCellRenderer();
                        r.setHorizontalAlignment(JLabel.CENTER);
                        table.setDefaultRenderer(Object.class, r);
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                } else {
                    dispose();
                }
            }
        });
    }

    class MyModel extends AbstractTableModel {
        private int row;
        private int column;
        private ResultSet rs;
        private Statement stmt;

        public MyModel(@Language("MySQL") String sql) {
            stmt = DBUtil.getStatement();
            try {
                rs = stmt.executeQuery(sql);
                rs.last();// 将光标移到最后一行
                row = rs.getRow();// 获取行号(最大行索引)
                ResultSetMetaData rsmd = rs.getMetaData();// 通过结果集对象来获取
                column = rsmd.getColumnCount();// 获取列数
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        @Override
        public int getColumnCount() {
            return column;
        }

        @Override
        public int getRowCount() {
            return row;
        }

        @Override
        public Object getValueAt(int rowIndex, int columnIndex) {
            Object value = null;
            try {
                rs.absolute(rowIndex + 1);
                value = rs.getString(columnIndex + 1);// 获取表里的数据
            } catch (Exception e) {
                e.printStackTrace();
            }
            return value;
        }
    }

    public Vector<String> getfeerepresent_name() throws SQLException {
        Vector<String> feerepresent_name = new Vector<String>();
        String sql = "SELECT express from fee_representation";
        Statement stmt = DBUtil.getStatement();
        ResultSet rs1 = stmt.executeQuery(sql);
        while (rs1.next()) {
            feerepresent_name.add(rs1.getString("express"));
        }
        DBUtil.closeMySQL();// 关闭连接
        return feerepresent_name;
    }

    public Vector<String> getEmployer_name() throws SQLException {
        Vector<String> Employer_name = new Vector<String>();
        String sql = "SELECT employer_name from employer ORDER BY employer_id";
        Statement stmt = DBUtil.getStatement();
        ResultSet rs1 = stmt.executeQuery(sql);
        while (rs1.next()) {
            Employer_name.add(rs1.getString("employer_name"));
        }
        DBUtil.closeMySQL();// 关闭连接
        return Employer_name;
    }

    public int selefeerepresent_id(String name) throws SQLException {
        @Language("MySQL")
        String sql = "SELECT id from fee_representation WHERE express='" + name + "';";
        int count = 0;
        Statement pr = DBUtil.getStatement();
        ResultSet rs = pr.executeQuery(sql);
        while (rs.next()) { // 遍历数据库的数据
            count = rs.getInt(1);
        }
        DBUtil.closeMySQL();// 关闭连接
        return count;
    }

    public int SelectEmlployerId(String employerName) throws SQLException {
        @Language("MySQL")
        String sql = "SELECT employer_id from employer WHERE employer_name='" + employerName + "';";
        int count = 0;
        Statement pr = DBUtil.getStatement();
        ResultSet rs = pr.executeQuery(sql);
        while (rs.next()) { // 遍历数据库的数据
            count = rs.getInt(1);
        }
        DBUtil.closeMySQL();// 关闭连接
        return count;
    }
}
